package com.iris.live.services.data.repositories.impl;

import com.google.common.collect.Maps;
import com.iris.live.services.data.repositories.ContractRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

@Repository
@Transactional
public class ContractRepositoryImpl extends BaseRepository implements ContractRepository {

	@Override
	public List getContractInfo(String proId) {
		Map<String, Object> params = Maps.newHashMap();

		String sqlStr = "SELECT a.`名称`, d.`车型详细`, b.`外观` `颜色`, "
				+ "b.`内饰`, e.`订金金额`, e.`无息退款天数`,b.折后价  "
				+ "FROM `LSH库存表` c "
				+ "LEFT JOIN `报价单表` AS b ON c.`报价编号` = b.`报价编号` "
				+ "LEFT JOIN `报价客户表` a ON b.`客户详细` = a.`编号` "
				+ "LEFT JOIN `LSH车型` AS d ON c.`车型编号` = d.`车型编号` "
				+ "LEFT JOIN `报价订单表` AS e ON b.`订单编号` = e.`订单编号` "
				+ "WHERE c.`生产号` = :proId";

		params.put("proId", proId);
		return getList(sqlStr, params);
	}

	@Override
	public List getCarOrderContract(String proId, String permit) {
		Map<String, Object> params = Maps.newHashMap();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT ");
		sb.append(" 	c.`订单客户姓名` 名称, ");
		sb.append(" 	d.`详细地址`, ");
		sb.append(" 	g.`电话号码` 电话, ");
		sb.append(" 	d.`身份证类型`, ");
		sb.append(" 	d.`身份证`, ");
		sb.append(" 	e.`经销商全称`, ");
		sb.append(" 	e.`经销商地址`, ");
		sb.append(" 	e.`经销商电话`, ");
		sb.append(" 	f.`车型详细`, ");
		sb.append(" 	b.`外观`, ");
		sb.append(" 	b.`内饰`, ");
		sb.append(" 	b.`折后价`, ");
		sb.append(" 	c.`订金交付日期`, ");
		sb.append(" 	c.`订金金额` ");
		sb.append(" FROM ( ");
		sb.append(" 	SELECT `报价单表`.* FROM `报价单表`     ");
		sb.append(" 	JOIN (    ");
		sb.append(" 		SELECT `报价单表`.`初始报价单号` FROM `报价单表`     ");
		sb.append(" 		JOIN `lsh库存表` ON `报价单表`.`报价编号` = `lsh库存表`.`报价编号` AND `lsh库存表`.`生产号` =  :proId  ");
		sb.append(" 	) `初始报价单号` ON `报价单表`.`初始报价单号` = `初始报价单号`.`初始报价单号`     ");
		sb.append(" 	WHERE `报价单表`.`是否驳回` = '否' AND `报价单表`.`是否还原点` = '是'      ");
		sb.append(" 	ORDER BY `报价单表`.`报价编号` DESC LIMIT 0,1  ");
		sb.append(" ) b ");
		sb.append(" LEFT JOIN `报价订单表` c ON c.`订单编号` = b.`订单编号` ");
		sb.append(" LEFT JOIN `报价客户表` d ON b.`客户详细` = d.`编号` ");
		sb.append(" LEFT JOIN `LSH经销商表` e ON e.`许可_BMBS` = :permit ");
		sb.append(" LEFT JOIN `LSH车型` f ON f.`车型编号` = b.`车型编号` ");
		sb.append(" LEFT JOIN `顾客记录汇总` g ON g.`顾客编号` = b.`顾客编号`; ");


		params.put("permit", permit);
		params.put("proId", proId);
		return getList(sb.toString(), params);
	}


	public List getKobeJamesOne(String proId, String permit) {
		Map<String, Object> params = Maps.newHashMap();

		String sqlStr = " SELECT      g.`经销商全称`  , \n" +
				"            d.`名称`, \n" +
				"         a.车架号,\n" +
				"  d.`身份证` ,      d.`详细地址`, \n" +
				"            f.`车型详细`  ,\n" +
				"CONCAT( e.`上牌省`, e.`上牌市`, e.`上牌区` ) `上牌区域` , \n" +
				"e.`交车日期` `购车时间`,\n" +
				"   b.`内饰`   \n" +
				" FROM `LSH库存表` a      \n" +
				"  LEFT JOIN `报价单表` b ON a.`报价编号` = b.`报价编号` \n" +
				"   LEFT JOIN `报价订单表` c ON c.`订单编号` = b.`订单编号` \n" +
				"           LEFT JOIN `报价交车表` AS e ON b.`交车` = e.`编号`  \n" +
				"   LEFT JOIN `报价客户表` d ON b.`客户详细` = d.`编号`    \n" +
				"  LEFT JOIN `LSH经销商表` g ON g.`许可_BMBS` =:permit   \n" +
				"   LEFT JOIN `LSH车型` f ON f.`车型编号` = a.`车型编号`    \n" +
				"  WHERE a.`生产号` =:proId       ";

		params.put("permit", permit);
		params.put("proId", proId);
		return getList(sqlStr, params);
	}


	public List getKobeJamesTwo(String proId, String permit) {
		Map<String, Object> params = Maps.newHashMap();

		String sqlStr = "   SELECT      g.`经销商全称`  ,            \n" +
				"  b.折后价,\n" +
				"         d.`名称`, \n" +
				"         a.车架号,    d.`详细地址`,   d.`身份证` , \n" +
				"          e.合同号     \n" +
				" FROM `LSH库存表` a      \n" +
				"   LEFT JOIN `报价单表` b ON a.`报价编号` = b.`报价编号` \n" +
				"  LEFT JOIN `报价订单表` c ON c.`订单编号` = b.`订单编号` \n" +
				"           LEFT JOIN `报价交车表` AS e ON b.`交车` = e.`编号`  \n" +
				"  LEFT JOIN `报价客户表` d ON b.`客户详细` = d.`编号`   \n" +
				" LEFT JOIN `LSH经销商表` g ON g.`许可_BMBS` =:permit \n" +
				" LEFT JOIN `LSH车型` f ON f.`车型编号` = a.`车型编号` \n" +
				" WHERE a.`生产号` =:proId   ";

		params.put("permit", permit);
		params.put("proId", proId);
		return getList(sqlStr, params);
	}

	public List getKobeJamesThree(String proId, String permit) {
		Map<String, Object> params = Maps.newHashMap();

		String sqlStr = " SELECT e.`合同号`,     \n" +
				"f.`名称` 买方,     \n" +
				" g.`经销商全称` 卖方,   \n" +
				"  a.`车型详细`,       \n" +
				" d.`订金交付日期`,     \n" +
				"   d.订金金额   \n" +
				"  FROM `LSH库存表` AS b \n" +
				" LEFT JOIN `报价单表` AS c ON b.`报价编号` = c.`报价编号` \n" +
				"   LEFT JOIN `报价订单表` AS d ON c.`订单编号` = d.`订单编号` \n" +
				"   LEFT JOIN `报价交车表` AS e ON c.`交车` = e.`编号` \n" +
				"   LEFT JOIN `报价客户表` AS f ON f.`编号` = c.`客户详细` \n" +
				"   LEFT JOIN `LSH车型` AS a ON a.`车型编号` = b.`车型编号` \n" +
				"   LEFT JOIN `LSH经销商表` AS g ON g.`许可_BMBS` =:permit    \n" +
				"  LEFT JOIN `销售项目设置` i ON (i.`设置项目` = '银行开户名称' AND i.`许可` =:permit)    \n" +
				"  LEFT JOIN `销售项目设置` j ON (j.`设置项目` = '开户银行名称' AND j.`许可` =:permit) \n" +
				"  LEFT JOIN `销售项目设置` k ON (k.`设置项目` = '银行开户账号' AND k.`许可` =:permit) \n" +
				"  WHERE b.`生产号` =:proId   ";

		params.put("permit", permit);
		params.put("proId", proId);
		return getList(sqlStr, params);
	}


	@Override
	public List getCarSaleContract(String proId, String permit) {
		Map<String, Object> params = Maps.newHashMap();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT   ");
		sb.append(" 	e.`合同号`,   ");
		sb.append(" 	IF(e.`开票客户姓名` IS NULL, f.`名称`, e.`开票客户姓名`) 买方,   ");
		sb.append(" 	'身份证' 身份证类型,   ");
		sb.append(" 	CASE    ");
		sb.append(" 	WHEN e.`开票客户身份证类型` = '公司' THEN e.`开票客户机构代码`    ");
		sb.append(" 	ELSE IF(e.`开票客户姓名` IS NULL, f.`身份证`, e.`开票客户身份证`) END AS 身份证, ");
		sb.append(" 	IF(e.`开票客户姓名` IS NULL, f.`电话`, e.`开票客户电话`)  电话, ");
		sb.append(" 	CONCAT(IFNULL(f.省份,'') ,IFNULL(f.城市,''),IFNULL(f.区域,''),IFNULL(f.`详细地址`,''))  详细地址,     ");
		sb.append(" 	if(b.批发商='MBCL','戴姆勒汽车公司',if(b.批发商='BBAC','北京奔驰汽车有限公司','戴姆勒汽车公司'))  as  生产商,      ");
		sb.append(" 	 g.`经销商全称` 卖方,      ");
		sb.append(" 	a.Variant_Desc_CN `车辆名称`,    ");
		sb.append(" 	c.`外观`,    ");
		sb.append(" 	a.`车型详细`,   ");
		sb.append(" 	c.`内饰`,    ");
		sb.append(" 	'1' `数量`,    ");
		sb.append(" 	b.`发动机号`,    ");
		sb.append(" 	b.`生产号`,    ");
		sb.append(" 	b.`车架号`,   ");
		sb.append(" 	e.`销售价格`,    ");
		sb.append(" 	d.`订金金额`,    ");
		sb.append(" 	e.`销售价格` - d.`订金金额` 余额,    ");
		sb.append(" 	IF (c.`状态` IN ('03', '04','12'), 'A', 'B') 付款方式,    ");
		sb.append(" 	i.`值` `开户名称`,    ");
		sb.append(" 	j.`值` `开户行`,     ");
		sb.append(" 	k.`值` `账号`,     ");
		sb.append(" 	c.`预计交车日期`,    ");
		sb.append(" 	CONCAT( e.`上牌省`, e.`上牌市`, e.`上牌区` ) `上牌区域`,    ");
		sb.append(" 	'' `保修年限`,     ");
		sb.append(" 	'' `退换年限`,     ");
		sb.append(" 	'' `附件包含`,     ");
		sb.append(" 	CONCAT(IFNULL(f.省份,'') ,IFNULL(f.城市,''),IFNULL(f.区域,''),IFNULL(f.`详细地址`,'')) `买方通讯地址`,    ");
		sb.append(" 	f.`电话` `买方电话`,     ");
		sb.append(" 	'' `买方传真`,     ");
		sb.append(" 	g.`经销商地址` `卖方通讯地址`,     ");
		sb.append(" 	g.`经销商电话` `卖方电话`,    ");
		sb.append(" 	'' `卖方传真`   ");
		sb.append(" FROM (    ");
		sb.append(" 	SELECT `报价单表`.*, jrsq.状态 FROM `报价单表`    ");
		sb.append(" 	JOIN (   ");
		sb.append(" 		SELECT `报价单表`.`初始报价单号` FROM `报价单表`    ");
		sb.append(" 		JOIN `lsh库存表` ON `报价单表`.`报价编号` = `lsh库存表`.`报价编号` AND `lsh库存表`.`生产号` = :proId ");
		sb.append(" 	) `初始报价单号` ON `报价单表`.`初始报价单号` = `初始报价单号`.`初始报价单号`    ");
		sb.append(" 	LEFT JOIN `lsh金融申请` jrsq ON `报价单表`.`初始报价单号` =jrsq.`初始报价单号`  ");
		sb.append(" 	WHERE `报价单表`.`是否驳回` = '否' AND `报价单表`.`是否还原点` = '是'     ");
		sb.append(" 	ORDER BY `报价单表`.`报价编号` DESC LIMIT 0,1    ");
		sb.append(" ) AS c  ");
		sb.append(" LEFT JOIN `LSH库存表` AS b ON b.库存编号 = c.库存编号  ");
		sb.append(" LEFT JOIN `报价订单表` AS d ON c.`订单编号` = d.`订单编号`   ");
		sb.append("  ");
		sb.append(" LEFT JOIN `报价交车表` AS e ON c.`交车` = e.`编号`   ");
		sb.append(" LEFT JOIN `报价客户表` AS f ON f.`编号` = c.`客户详细`   ");
		sb.append(" LEFT JOIN `LSH车型` AS a ON a.`车型编号` = b.`车型编号`   ");
		sb.append(" LEFT JOIN `LSH经销商表` AS g ON g.`许可_BMBS` = :permit    ");
		sb.append(" LEFT JOIN `销售项目设置` i ON (i.`设置项目` = '银行开户名称' AND i.`许可` = :permit)   ");
		sb.append(" LEFT JOIN `销售项目设置` j ON (j.`设置项目` = '开户银行名称' AND j.`许可` = :permit)   ");
		sb.append(" LEFT JOIN `销售项目设置` k ON (k.`设置项目` = '银行开户账号' AND k.`许可` = :permit)   ");
		sb.append(" WHERE b.`生产号` = :proId ");
		params.put("permit", permit);
		params.put("proId", proId);
		return getList(sb.toString(), params);
	}

	@Override
	public List getCarSaleAttachment(String proId, String permit) {
		Map<String, Object> params = Maps.newHashMap();

		String sqlStr = "SELECT a.*,b.* " +
				"FROM (" +
				"SELECT q.*, w.* " +
				"FROM" +
				" lsh随车项目表 q " +
				"RIGHT JOIN (" +
				" SELECT" +
				"  d.`合同号`, " +
				"  d.`开票客户姓名`, " +
				"  d.`合同签约日期`, " +
				"  j.`订单客户姓名`, " +
				"  j.`订单号`, " +
				"  c.`贴息金额` as 厂家贴息, " +
				"  e.`随车名称` as 项目, " +
				"  e.`随车内容` as 内容, " +
				"  e.`随车部门`, " +
				"  e.`随车类型`, " +
				"  h.`名称` as 售价为0的精品, " +
				"  h.`数量` as 售价为0的精品数量, " +
				"  h.`原价` as 售价为0的精品价值, " +
				"  f.`延保原价` as 售价为0的五星延保, " +
				"  i.`顾客姓名` " +
				" FROM " +
				"  `LSH库存表` a " +
				" LEFT JOIN `报价单表` b ON a.`报价编号` = b.`报价编号` " +
				" LEFT JOIN `顾客记录汇总` i ON i.`顾客编号`=b.`顾客编号` " +
				" LEFT JOIN `lsh金融申请附属` c ON b.`初始报价单号` = c.`初始报价单号` " +
				" LEFT JOIN `报价订单表` j ON j.`订单编号`=b.`订单编号` " +
				" LEFT JOIN `报价交车表` d ON b.`交车` = d.`编号` " +
				" LEFT JOIN `报价随车表` e ON b.`报价编号` = e.`报价编号` " +
				" AND (" +
				"  e.`随车类型` = '经销商'" +
				"  OR e.`随车类型` = '厂家'" +
				" )" +
				" LEFT JOIN `报价五星延保` f ON b.`延保编号` = f.`延保编号`" +
				" AND f.`延保折后价` = 0" +
				" LEFT JOIN `报价精品表` g ON b.`精品` = g.`编号`" +
				" LEFT JOIN `报价精品详细表` h ON g.`编号` = h.`编号` " +
				" AND h.`折后价` = 0" +
				" WHERE" +
				"  a.`生产号` = :proId " +
				") w ON w.`项目` = q.`随车` AND w.`随车类型` = q.`类型` " +
				"ORDER BY  q.`类型`, q.`随车编号` " +
				") a " +
				"JOIN" +
				"(" +
				"SELECT 经销商全称 FROM `lsh经销商表` WHERE `许可_BMBS`= :permit " +
				") b";
		params.put("proId", proId);
		params.put("permit", permit);
		return getList(sqlStr, params);
	}

	@Override
	public List getInstallationContract(String proId, String permit) {
		Map<String,Object> params = Maps.newHashMap();
		
		String sqlStr = "SELECT g.`合同号`, d.`名称`, d.`电话`, d.`身份证`, "
				+ 				"e.`经销商全称`, i.`值` `开户名称`,  j.`值` `开户行`,  "
				+ 				"k.`值` `账号` FROM `LSH库存表` a "
				+ 		"LEFT JOIN `报价单表` b ON a.`报价编号` = b.`报价编号` "
				+ 		"LEFT JOIN `报价客户表` d ON b.`客户详细` = d.`编号` "
				+ 		"LEFT JOIN `LSH经销商表` e ON e.`许可_BMBS` = :permit "
				+ 		"LEFT JOIN `报价交车表` g ON g.`编号` = b.`交车` "
				+ 		"LEFT JOIN `销售项目设置` i ON (i.`设置项目` = '银行开户名称' AND i.`许可` = :permit) "
				+ 		"LEFT JOIN `销售项目设置` j ON (j.`设置项目` = '开户银行名称' AND j.`许可` = :permit) "
				+ 		"LEFT JOIN `销售项目设置` k ON (k.`设置项目` = '银行开户账号' AND k.`许可` = :permit) "
				+ 		"WHERE a.`生产号` = :proId";
	
		params.put("permit",permit);
		params.put("proId",proId);
		return getList(sqlStr, params);
	}

	@Override
	public List getCarSaleAttachmentWithLoss(String proId) {
		Map<String,Object> params = Maps.newHashMap();
		
		String sqlStr = "SELECT d.`名称`,e.`车型详细`,a.`发动机号`,\n" +
				"IF (a.`车架号` LIKE '%IRISTechNV_%' OR a.`车架号` LIKE '%IRISTechnologies%','',a.`车架号`) AS 车架号\n" +
				"FROM `LSH库存表` a\n" +
				"LEFT JOIN `LSH车型` e ON e.`车型编号` = a.`车型编号`\n" +
				"LEFT JOIN `报价单表` b ON a.`报价编号` = b.`报价编号`\n" +
				"LEFT JOIN `报价客户表` d ON b.`客户详细` = d.`编号`\n" +
				"WHERE a.`生产号` =:proId";
		
		params.put("proId",proId);
		return getList(sqlStr, params);
	}

	@Override
	public List getPickCarWarrant(String proId) {
		Map<String,Object> params = Maps.newHashMap();
		
		String sqlStr = "SELECT '' `公司`, c.`名称`,  '' `提车代理人`, '' `提车代理人身份证`, d.`合同号` "
				+ 		"FROM `LSH库存表` a "
				+	 	"LEFT JOIN `报价单表` b ON a.`报价编号` = b.`报价编号` "
				+ 		"LEFT JOIN `报价客户表` c ON b.`客户详细` = c.`编号` "
				+ 		"LEFT JOIN `报价交车表` d ON d.`编号` = b.`交车` "
				+ 		"WHERE a.`生产号` = :proId";
		params.put("proId",proId);
		return getList(sqlStr, params);
	}

	@Override
	public List getProxyAgreement(String proId, String permit) {
		Map<String,Object> params = Maps.newHashMap();

		String sqlStr = "SELECT e.`名称`, b.`经销商全称`, a.`车架号`, a.`发动机号`,e.身份证,e.电话,b.经销商电话," +
				"       d.交车日期,  x.`值` `开户名称`,  y.`值` `开户行`,  z.`值`  `账号`,     "
				+ 				"g.`费用折后价` + h.`金融服务费` `代办费`, h.`金融服务费`,"
				+ 				"'' `上牌代办费`, i.`交强险手续费` + i.`商业险手续费` `带缴保险费`, "
				+ 				"CONCAT('交强险',',','商业险') `险种`, "
				+ 				"CONCAT(i.`交强险保险公司`,',',i.`商业险保险公司`)`保险公司`, "
				+ 				"i.`交强险开始日期` `保险开始日期`, j.`折后价` `车船税`, "
				+ 				"ROUND((d.`销售价格`)/1.17 * 0.1, 2) `代缴购置税` "
				+ 		"FROM `LSH库存表` a "
				+ 		"LEFT JOIN `LSH经销商表` b ON b.`许可_BMBS` = :permit "
				+ 		"LEFT JOIN `报价单表` c ON c.`报价编号` = a.`报价编号` "
				+ 		"LEFT JOIN `报价交车表` d ON d.`编号` = c.`交车` "
				+ 		"LEFT JOIN `报价客户表` e ON e.`编号` = c.`客户详细` "
				+ 		"LEFT JOIN `LSH车型` f ON f.`车型编号` = a.`车型编号` "
				+ 		"LEFT JOIN `报价费用详细表` g ON (g.`费用编号` = c.`费用编号` AND g.`费用名称` = '其他服务费') "
				+ 		"LEFT JOIN `报价金融表` h ON h.`金融编号` = c.`金融` "
				+ 		"LEFT JOIN `报价保险表` i ON i.`保险编号` = c.`保险编号` "
				+ 		"LEFT JOIN `报价保险详细表` j ON (j.`保险编号` = i.`保险编号` AND j.`险种` = '车船税') "
				+ "LEFT JOIN `销售项目设置` x ON (x.`设置项目` = '银行开户名称' AND x.`许可` = :permit) "
				+ "LEFT JOIN `销售项目设置` y ON (y.`设置项目` = '开户银行名称' AND y.`许可` = :permit) "
				+ "LEFT JOIN `销售项目设置` z ON (z.`设置项目` = '银行开户账号' AND z.`许可` = :permit) "
				+ 		"WHERE a.`生产号` = :proId ";
		
		params.put("permit",permit);
		params.put("proId",proId);
		return getList(sqlStr, params);
	}

	@Override
	public List getInvoiceAdvice(String proId, String permit) {
		Map<String,Object> params = Maps.newHashMap();
		
		String sqlStr = "SELECT g.`经销商全称` `公司`, e.`合同号`,'' `发票抬头`,'' `销售经理` "
				+ 		"FROM `LSH库存表` AS b "
				+ 		"LEFT JOIN `报价单表` AS c ON b.`报价编号` = c.`报价编号` "
				+ 		"LEFT JOIN `报价交车表` AS e ON c.`交车` = e.`编号` "
				+ 		"LEFT JOIN `LSH经销商表` AS g ON g.`许可_BMBS` = :permit "
				+ 		"WHERE b.`生产号` = :proId";
		
		params.put("permit",permit);
		params.put("proId",proId);
		return getList(sqlStr, params);
	}

	@Override
	public List getPayInsuranceProxy(String proId, String permit) {
		Map<String,Object> params = Maps.newHashMap();
		
		String sqlStr = "SELECT g.`经销商全称` `经销商`, a.`商业险保单号` `保单编号`, "
				+ 				"a.`保险折后价` `首期保险费`, '续期手续费' `续期保险费`, "
				+ 				"'续保期限' `续保期限`, f.`身份证`, f.`身份证类型`, f.`电话` "
				+ 		"FROM `LSH库存表` AS b "
				+ 		"LEFT JOIN `报价单表` AS c ON b.`报价编号` = c.`报价编号` "
				+ 		"LEFT JOIN `报价客户表` AS f ON f.`编号` = c.`客户详细` "
				+ 		"LEFT JOIN `LSH经销商表` AS g ON g.`许可_BMBS` = :permit "
				+ 		"LEFT JOIN `报价保险表` a ON a.`保险编号` = c.`保险编号` "
				+ 		"WHERE b.`生产号` = :proId";
		
		params.put("permit",permit);
		params.put("proId",proId);
		return getList(sqlStr, params);
	}

	@Override
	public List getSecurityAgreement(String proId, String permit) {
		Map<String,Object> params = Maps.newHashMap();
		
		String sqlStr = "SELECT  b.`经销商全称`, b.`经销商地址`, e.`名称`, e.`身份证`, "
				+ 				"e.`详细地址`, d.`交车日期` `购车时间`, f.`车型详细`, a.`欧版底盘号` `底盘号` "
				+ 		"FROM `LSH库存表` a "
				+ 		"LEFT JOIN `LSH经销商表` b ON b.`许可_BMBS` = :permit "
				+ 		"LEFT JOIN `报价单表` c ON c.`报价编号` = a.`报价编号` "
				+ 		"LEFT JOIN `报价交车表` d ON d.`编号` = c.`交车` "
				+ 		"LEFT JOIN `报价客户表` e ON e.`编号` = c.`客户详细` "
				+ 		"LEFT JOIN `LSH车型` f ON f.`车型编号` = a.`车型编号` "
				+ 		"WHERE a.`生产号` = :proId";
		
		params.put("permit",permit);
		params.put("proId",proId);
		return getList(sqlStr, params);
	}
}
